#!/usr/bin/python
# -*- coding: UTF-8 -*-
# 文件名：14-mysql.py

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "user", "password", "database", charset='utf8')

# 使用cursor()方法获取操作游标 
cursor = db.cursor()


# ===1=查询单行数据信息====

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

print "Database version : %s " % data


# ===2=查询多行数据信息====
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 打印结果
      print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"
   
# ===4=创建数据库====

# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# 创建数据表SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)


# ===3=插入语句====
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# ===4=删除语句====
sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

# ===5=更新语句====
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()



      
# ===关闭数据库连接===

db.close()




'''
实战案例

conn = MySQLdb.connect(host=houtaidic[plname], user='user', passwd='psssword',port=3306, charset='utf8')
try:
    cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
    conn.select_db('h5_houtai')
    sql="SELECT ip,gameport from serverinfo WHERE pf=\'%s\' and zoneid<8000 group by ip" % plname
    cur.execute(sql)
    result = cur.fetchall()
    cur.close()
    except MySQLdb.Error,e:
        print "Mysql Error %d:%s"%(e.args[0],e.args[1])
        
    #添加任务信息，
    for i in range(len(result)):
        gameDic[result[i]['ip']] = get_sh_commmand(plname)
   #for k in result:
   #game[k]=get()
    print gameDic
'''


# 1===测试通过 查询版本
'''
#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "jygsuser", "Aa12345678", "ops", charset='utf8')

# 使用cursor()方法获取操作游标 
cursor = db.cursor()


# ===1=查询单行数据信息====

# 使用execute方法执行SQL语句
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取一条数据
data = cursor.fetchone()

#print "Database version : %s " % data
print "Database version : {0} ".format(data)


db.close()
'''

# 2===测试通过 创建数据库

'''#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "jygsuser", "Aa12345678", "ops", charset='utf8')

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# ===1=查询单行数据信息====

# 使用execute方法执行SQL语句
sql = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

cursor.execute(sql)

db.close()
'''

# 3===测试通过 插入数据

'''#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "jygsuser", "Aa12345678", "ops", charset='utf8')

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('tom', 'jeck', 30, 'W', 1000)"""
try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

db.close()
'''

# 4===测试通过 删除数据

'''#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "jygsuser", "Aa12345678", "ops", charset='utf8')

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# ===4=删除语句====
#sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (25)
sql = "DELETE FROM EMPLOYEE WHERE AGE > {0}".format(25)

try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交修改
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()


db.close()'''


# 5===测试通过 更新数据
'''
#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "jygsuser", "Aa12345678", "ops", charset='utf8')

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '{0}'".format('M')
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

db.close()
'''

# 6===测试通过 查询多条数据

'''#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("127.0.0.1", "jygsuser", "Aa12345678", "ops", charset='utf8')

# 使用cursor()方法获取操作游标
#查询总结果为元组类型，默认每一项数据也是元组

cursor = db.cursor()

# ===2=查询多行数据信息====
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %s" % (100)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表，
   
   # ((u'Mac', u'Mohan', 24L, u'M', 2000.0), (u'tom', u'jeck', 30L, u'W', 1000.0))
   
   results = cursor.fetchall()

   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      print "fname={0},lname={1},age={2},sex={3},income={4}".format(fname, lname, age, sex, income)
except:
   print "Error: unable to fecth data"

db.close()
'''


'''# 更改每一项的
cursor = db.cursor(cursorclass=MySQLdb.cursors.DictCursor)
({'FIRST_NAME': u'Mac', 'LAST_NAME': u'Mohan', 'AGE': 24L, 'INCOME': 2000.0, 'SEX': u'M'}, {'FIRST_NAME': u'tom', 'LAST_NAME': u'jeck', 'AGE': 30L, 'INCOME': 1000.0, 'SEX': u'W'})

fname = row['FIRST_NAME']
lname = row['LAST_NAME']
age = row['AGE']
sex = row['SEX']
income = row['INCOME']
'''
